﻿using System;
using System.Collections.Generic;
using System.Linq;
using NetSurveillance.WebApp.DataServices.Models;
using ServiceStack.OrmLite;

namespace NetSurveillance.WebApp.DataServices.Implementations
{
    public class DataIMRecordService : BaseService<DataIMRecord>
    {
        public DataIMRecordService(string connStr, IOrmLiteDialectProvider provider = null)
            : base(connStr, provider)
        {
        }

        public int CaclPageIndex(long dataIMRecordId, int pageSize)
        {
            using (var db = DbFactory.OpenDbConnection())
            {
                const string calcPageIndexSql =
                    @"select rownum from (
                            select row_number() over (order by DIMR.""Id"" desc) as rownum,DIMR.""Id"" 
                            from ""DataIMRecords"" as DIMR
                            left join ""Hosts"" as Hst on DIMR.""HostId""=Hst.""Id"" 
                            left join ""Tasks"" as Tsk on DIMR.""TaskId""=Tsk.""Id"" 
                            left join ""Cases"" as Cases on Tsk.""CaseId""=Cases.""Id""
                            where DIMR.""StatusToken"" != 0
                             and DIMR.""LocalNumber"" = 
                                (select ""LocalNumber"" from ""DataIMRecords"" as DIMR1 where DIMR1.""Id""=:dataIMRecordId)
                            and DIMR.""RemoteNumber"" = 
                                (select ""RemoteNumber"" from ""DataIMRecords"" as DIMR2 where DIMR2.""Id""=:dataIMRecordId)
                    ) as AllDimr
                    where AllDimr.""Id""=:dataIMRecordId";
                int? rowNum = db.QueryScalar<int?>(calcPageIndexSql, new {dataIMRecordId});
                int pageIndex = 1;
                if (rowNum.HasValue && (rowNum.Value / pageSize) > 0)
                {
                    pageIndex = (rowNum.Value / pageSize) + ((rowNum.Value % pageSize) > 0 ? 1 : 0);
                }
                return pageIndex;
            }
        }

        public IList<DataIMRecordResult> GetDataIMRecords(int pageIndex, int pageSize,
                                                    DataIMRecordFilter dimrFilter, out int? totalCount)
        {
            using (var db = DbFactory.OpenDbConnection())
            {
                string searchSql =
                    @"select Cases.""Name"" as ""CaseName"",Tsk.""Name"" as ""TaskName"",
                        Hst.""HostName"",
                        (case when lower(DIMR.""Direction"") = 'send' then
                            (case when AAL.""Alias"" is null then DIMR.""LocalNumber"" else  AAL.""Alias"" end)
                            else (case when AAR.""Alias"" is null then DIMR.""RemoteNumber"" else AAR.""Alias"" end)
                        end) as ""Sender"",
                        (case when lower(DIMR.""Direction"") = 'send' then
                            (case when AAR.""Alias"" is null then DIMR.""RemoteNumber"" else AAR.""Alias"" end)
                            else (case when AAL.""Alias"" is null then DIMR.""LocalNumber"" else  AAL.""Alias"" end)
                        end) as ""Receiver"",
                        DIMR.* from ""DataIMRecords"" as DIMR
                        left join ""AccountAliases"" as AAL on DIMR.""LocalNumber""=AAL.""Account"" 
                        left join ""AccountAliases"" as AAR on DIMR.""RemoteNumber""=AAR.""Account"" 
                        left join ""Hosts"" as Hst on DIMR.""HostId""=Hst.""Id"" 
                        left join ""Tasks"" as Tsk on DIMR.""TaskId""=Tsk.""Id"" 
                        left join ""Cases"" as Cases on Tsk.""CaseId""=Cases.""Id"" 
                        where DIMR.""StatusToken"" != 0";
                string searchTotalCountSql =
                    @"select count(DIMR.""Id"") from ""DataIMRecords"" as DIMR
                        left join ""Hosts"" as Hst on DIMR.""HostId""=Hst.""Id"" 
                        left join ""Tasks"" as Tsk on DIMR.""TaskId""=Tsk.""Id"" 
                        left join ""Cases"" as Cases on Tsk.""CaseId""=Cases.""Id""
                        where DIMR.""StatusToken"" != 0";
                string whereSql = GenerateWhereSql(dimrFilter);
                searchSql += whereSql;
                if (!string.IsNullOrWhiteSpace(dimrFilter.OrderField) &&
                    !string.IsNullOrWhiteSpace(dimrFilter.Order))
                    searchSql += string.Format(" order by \"{0}\" {1}", dimrFilter.OrderField,
                                               dimrFilter.Order);
                if (!dimrFilter.IsTakeAll)
                    searchSql += " limit :pageSize offset :skipCount";
                
                searchTotalCountSql += whereSql;
                var dynamicParams = new
                    {
                        dimrFilter.CaseId,
                        dimrFilter.TaskId,
                        pageSize,
                        skipCount = pageSize*(pageIndex - 1),
                        dimrFilter.LocalNumber,
                        dimrFilter.RemoteNumber,
                        dimrFilter.GroupNumber,
                        dimrFilter.DataIMRecordId
                    };
                var dataIMRecords = db.SqlList<DataIMRecordResult>(searchSql, dynamicParams);
                totalCount = db.QueryScalar<int?>(searchTotalCountSql, dynamicParams);
                
                return dataIMRecords;
            }
        }

        public IList<DataIMRecordResult> GetDataIMRecords(long taskId, long maxRecordId,
                                                          string localNumber, string remoteNumber,
                                                          string groupNumber)
        {
            using (var db = DbFactory.OpenDbConnection())
            {
                string searchSql =
                    @"select Cases.""Name"" as ""CaseName"",Tsk.""Name"" as ""TaskName"",
                        Hst.""HostName"",
                        (case when lower(DIMR.""Direction"") = 'send' then
                            (case when AAL.""Alias"" is null then DIMR.""LocalNumber"" else  AAL.""Alias"" end)
                            else (case when AAR.""Alias"" is null then DIMR.""RemoteNumber"" else AAR.""Alias"" end)
                        end) as ""Sender"",
                        (case when lower(DIMR.""Direction"") = 'send' then
                            (case when AAR.""Alias"" is null then DIMR.""RemoteNumber"" else AAR.""Alias"" end)
                            else (case when AAL.""Alias"" is null then DIMR.""LocalNumber"" else  AAL.""Alias"" end)
                        end) as ""Receiver"",
                        DIMR.* from ""DataIMRecords"" as DIMR 
                        left join ""AccountAliases"" as AAL on DIMR.""LocalNumber""=AAL.""Account"" 
                        left join ""AccountAliases"" as AAR on DIMR.""RemoteNumber""=AAR.""Account""  
                        left join ""Hosts"" as Hst on DIMR.""HostId""=Hst.""Id"" 
                        left join ""Tasks"" as Tsk on DIMR.""TaskId""=Tsk.""Id"" 
                        left join ""Cases"" as Cases on Tsk.""CaseId""=Cases.""Id""
                        where DIMR.""StatusToken"" != 0 and DIMR.""TaskId""=:taskId and DIMR.""Id"">:maxRecordId "
                    +
                    (string.IsNullOrWhiteSpace(localNumber)
                         ? string.Empty
                         : @" and DIMR.""LocalNumber""=:localNumber")
                    +
                    (string.IsNullOrWhiteSpace(remoteNumber)
                         ? string.Empty
                         : @" and DIMR.""RemoteNumber""=:remoteNumber")
                    +
                    (string.IsNullOrWhiteSpace(groupNumber)
                         ? string.Empty
                         : @" and DIMR.""RemoteNumber""=:groupNumber")
                    + @" order by DIMR.""Id"" Desc";
                return db.SqlList<DataIMRecordResult>(searchSql,
                                                      new
                                                          {
                                                              taskId,
                                                              maxRecordId,
                                                              localNumber,
                                                              remoteNumber,
                                                              groupNumber
                                                          });
            }
        }

        public IList<DataIMRecordResult> GetDataIMRecords(IMDataSearch imDataSearch,
                                                          DataIMRecordFilter dimrFilter,
                                                          long[] hostIds, int pageIndex,
                                                          int pageSize, out int? totalCount)
        {
            using (var db = DbFactory.OpenDbConnection())
            {
                string searchSql =
                    @"select Cases.""Name"" as ""CaseName"",Tsk.""Name"" as ""TaskName"",
                        Hst.""HostName"",
                        (case when lower(DIMR.""Direction"") = 'send' then
                            (case when AAL.""Alias"" is null then DIMR.""LocalNumber"" else  AAL.""Alias"" end)
                            else (case when AAR.""Alias"" is null then DIMR.""RemoteNumber"" else AAR.""Alias"" end)
                        end) as ""Sender"",
                        (case when lower(DIMR.""Direction"") = 'send' then
                            (case when AAR.""Alias"" is null then DIMR.""RemoteNumber"" else AAR.""Alias"" end)
                            else (case when AAL.""Alias"" is null then DIMR.""LocalNumber"" else  AAL.""Alias"" end)
                        end) as ""Receiver"",
                        DIMR.* from ""DataIMRecords"" as DIMR
                        left join ""AccountAliases"" as AAL on DIMR.""LocalNumber""=AAL.""Account"" 
                        left join ""AccountAliases"" as AAR on DIMR.""RemoteNumber""=AAR.""Account"" 
                        left join ""Hosts"" as Hst on DIMR.""HostId""=Hst.""Id"" 
                        left join ""Tasks"" as Tsk on DIMR.""TaskId""=Tsk.""Id"" 
                        left join ""Cases"" as Cases on Tsk.""CaseId""=Cases.""Id""
                        where DIMR.""StatusToken"" != 0";
                string searchTotalCountSql =
                    @"select count(DIMR.""Id"") from ""DataIMRecords"" as DIMR
                        left join ""Hosts"" as Hst on DIMR.""HostId""=Hst.""Id"" 
                        left join ""Tasks"" as Tsk on DIMR.""TaskId""=Tsk.""Id"" 
                        left join ""Cases"" as Cases on Tsk.""CaseId""=Cases.""Id""
                        where DIMR.""StatusToken"" != 0";
                string whereSql = GenerateWhereSql(dimrFilter) +
                                  GenerateWhereSql(imDataSearch, hostIds);
                searchSql += whereSql;

                if (!dimrFilter.IsTakeAll)
                    searchSql += " limit :pageSize offset :skipCount";
                if (!string.IsNullOrWhiteSpace(dimrFilter.OrderField) &&
                    !string.IsNullOrWhiteSpace(dimrFilter.Order))
                    searchSql += " order by :orderField :orderSort";
                searchTotalCountSql += whereSql;
                var dynamicParams = new
                {
                    dimrFilter.CaseId,
                    dimrFilter.TaskId,
                    pageSize,
                    skipCount = pageSize * (pageIndex - 1),
                    dimrFilter.LocalNumber,
                    dimrFilter.RemoteNumber,
                    dimrFilter.GroupNumber,
                    dimrFilter.OrderField,
                    OrderSort = dimrFilter.Order,
                    dimrFilter.DataIMRecordId
                };
                var dataIMRecords = db.SqlList<DataIMRecordResult>(searchSql, dynamicParams);
                totalCount = db.QueryScalar<int?>(searchTotalCountSql, dynamicParams);

                return dataIMRecords;
            }
        }

        public IList<DataIMRecordResult> GetLocalIMs(long taskId, string protocol = "qq")
        {
            using (var db = DbFactory.OpenDbConnection())
            {
                const string searchSql =
                    @"select distinct DIMR.""LocalNumber"",
                        (case when AAL.""Alias"" is null then DIMR.""LocalNumber"" else AAL.""Alias"" end) as ""LocalName""
                        from ""DataIMRecords"" as DIMR
                        left join ""AccountAliases"" as AAL on DIMR.""LocalNumber""=AAL.""Account"" 
                        where DIMR.""StatusToken"" != 0 and DIMR.""TaskId""=:taskId and lower(DIMR.""Protocol"")=:protocol";
                protocol = protocol.ToLower();
                return db.SqlList<DataIMRecordResult>(searchSql, new { taskId, protocol });
            }
        }

        public IList<DataIMRecordResult> GetRemoteIMs(long taskId, string protocol = "qq")
        {
            using (var db = DbFactory.OpenDbConnection())
            {
                const string searchSql =
                    @"select distinct DIMR.""RemoteNumber"",
                        (case when AAR.""Alias"" is null then DIMR.""RemoteNumber"" else AAR.""Alias"" end) as ""RemoteName""
                        from ""DataIMRecords"" as DIMR
                        left join ""AccountAliases"" as AAR on DIMR.""RemoteNumber""=AAR.""Account""
                        where DIMR.""StatusToken"" != 0 and DIMR.""TaskId""=:taskId and lower(DIMR.""Protocol"")=:protocol";
                protocol = protocol.ToLower();
                return db.SqlList<DataIMRecordResult>(searchSql, new { taskId, protocol });
            }
        }

        public IList<IMRecordGroup> GroupRecords(long taskId)
        {
            using (var db = DbFactory.OpenDbConnection())
            {
                const string searchSql =
                    @"select GR.*,
                        (case when AAL.""Alias"" is null then GR.""LocalNumber"" else AAL.""Alias"" end) as ""LocalName"",
                        (case when AAR.""Alias"" is null then GR.""RemoteNumber"" else AAR.""Alias"" end) as ""RemoteName""
                    from (select DIMR.""LocalNumber"",DIMR.""RemoteNumber"",count(DIMR.""RemoteNumber"") as ""Count"" 
                            from ""DataIMRecords"" as DIMR
                            where DIMR.""StatusToken"" != 0 and DIMR.""TaskId"" = :taskId 
                                and (DIMR.""GroupNumber"" is null or trim(DIMR.""GroupNumber"")='') 
                            group by DIMR.""LocalNumber"",DIMR.""RemoteNumber"") as GR
                    left join ""AccountAliases"" as AAL on GR.""LocalNumber""=AAL.""Account""
                    left join ""AccountAliases"" as AAR on GR.""RemoteNumber""=AAR.""Account""";
                var dynamicParams = new {taskId};
                var noGroupNumberList = db.SqlList<IMRecordGroup>(searchSql, dynamicParams);

                const string searchGroupSql =
                    @"select GR.*,
                        (case when AA.""Alias"" is null then GR.""GroupNumber"" else AA.""Alias"" end) as ""GroupName"" 
                        from (select DIMR.""LocalNumber"",DIMR.""GroupNumber"",
                            count(DIMR.""GroupNumber"") as ""Count"" 
                            from ""DataIMRecords"" as DIMR
                            where DIMR.""StatusToken"" != 0 and DIMR.""TaskId"" = :taskId 
                                and (DIMR.""GroupNumber"" is not null and trim(DIMR.""GroupNumber"")!='') 
                            group by DIMR.""LocalNumber"",DIMR.""GroupNumber"") as GR 
                    left join ""AccountAliases"" as AA on GR.""GroupNumber""=AA.""Account"" and AA.""AccountType""='Group'";
                noGroupNumberList.AddRange(db.SqlList<IMRecordGroup>(searchGroupSql, dynamicParams));
                return noGroupNumberList;
            }
        }

        public IList<IMRecordGroup> GroupRecords(long taskId, string localNumber)
        {
            using (var db = DbFactory.OpenDbConnection())
            {
                const string searchSql =
                    @"select DIMR.""RemoteNumber"",
                        (select AAR.""Alias"" from ""AccountAliases"" as AAR where AAR.""Account""=DIMR.""LocalNumber"" limit 1) as ""RemoteName"",
                        count(DIMR.""RemoteNumber"") as ""Count"" 
                        from ""DataIMRecords"" as DIMR
                        where DIMR.""StatusToken"" != 0 and DIMR.""TaskId"" = :taskId 
                            and DIMR.""LocalNumber""=:localNumber 
                            and (DIMR.""GroupNumber"" is null or trim(DIMR.""GroupNumber"")='') 
                        group by DIMR.""RemoteNumber""";
                return db.SqlList<IMRecordGroup>(searchSql, new { taskId, localNumber });
            }
        }

        public IList<IMRecordGroup> GetGroups(long taskId)
        {
            using (var db = DbFactory.OpenDbConnection())
            {
                const string searchSql =
                    @"select GR.*,
                        (case when AA.""Alias"" is null then GR.""GroupNumber"" else AA.""Alias"" end) as ""GroupName"" 
                        from (select DIMR.""GroupNumber"",count(DIMR.""GroupNumber"") as ""Count"" 
                            from ""DataIMRecords"" as DIMR 
                            where DIMR.""StatusToken"" != 0 and DIMR.""GroupNumber"" is not null and DIMR.""GroupNumber"" != ''
                            and DIMR.""TaskId"" = :taskId
                            group by DIMR.""GroupNumber"") as GR 
                       left join ""AccountAliases"" as AA on GR.""GroupNumber""=AA.""Account"" and AA.""AccountType""='Group'";
                return db.SqlList<IMRecordGroup>(searchSql, new { taskId });
            }
        }
 
        public IList<DataRecord> GetQQFileResults(string localNum, string groupNum)
        {
            using (var db = DbFactory.OpenDbConnection())
            {
                string searchSql =
                    @"select * from ""DataRecords"" as DR
                        left join ""DataIMRecords"" as DIMR on DR.""DataIMRecordId""=DIMR.""Id""
                      where DR.""StatusToken""!=0 and DR.""FilePath"" is not null and DR.""Protocol""='qqfile' ";
                var paramDic = new Dictionary<string, object>();
                if (!string.IsNullOrWhiteSpace(localNum))
                {
                    searchSql +=
                        @" and DIMR.""LocalNumber""=:localNum";
                    paramDic.Add(":localNum", localNum);
                }
                else if (!string.IsNullOrWhiteSpace(groupNum))
                {
                    searchSql +=
                        @" and DIMR.""GroupNumber""=:groupNum";
                    paramDic.Add(":groupNum", groupNum);
                }
                return db.SqlList<DataRecord>(searchSql, paramDic);
            }
        } 

        private string GenerateWhereSql(DataIMRecordFilter dimrFilter)
        {
            string whereSql = string.Empty;
            if (dimrFilter.CaseId.HasValue || (dimrFilter.TaskId.HasValue && dimrFilter.TaskId.Value > 0))
            {
                whereSql += dimrFilter.CaseId.HasValue
                                 ? @" and DIMR.""TaskId"" in (select ""Id"" from ""Tasks"" where ""CaseId""=:caseId) "
                                 : @" and DIMR.""TaskId""=:taskId ";
            }
            if (!string.IsNullOrWhiteSpace(dimrFilter.GroupNumber))
                whereSql += " and DIMR.\"GroupNumber\" = :groupNumber";
            if (!string.IsNullOrWhiteSpace(dimrFilter.LocalNumber))
                whereSql += " and DIMR.\"LocalNumber\" = :localNumber";
            if (!string.IsNullOrWhiteSpace(dimrFilter.RemoteNumber))
                whereSql += " and DIMR.\"RemoteNumber\" = :remoteNumber";
            if (dimrFilter.DataIMRecordId.HasValue)
                whereSql +=
                    " and DIMR.\"LocalNumber\" = (select \"LocalNumber\" from \"DataIMRecords\" where \"Id\"=:dataIMRecordId)" +
                    " and DIMR.\"RemoteNumber\" = (select \"RemoteNumber\" from \"DataIMRecords\" where \"Id\"=:dataIMRecordId)";
            return whereSql;
        }

        private string GenerateWhereSql(IMDataSearch imSearch, long[] hostIds)
        {
            string whereSql = string.Empty;

            if (hostIds != null && hostIds.Any())
            {
                whereSql += string.Format(" and DIMR.\"HostId\" in ({0})", string.Join(",", hostIds));
            }
            if (imSearch != null)
            {
                if (!string.IsNullOrWhiteSpace(imSearch.Protocol))
                    whereSql += " and DIMR.\"Protocol\" = {0}".Params(imSearch.Protocol);
                if (!string.IsNullOrWhiteSpace(imSearch.Content))
                    whereSql +=
                        " and DIMR.\"Content\" like {0}".Params(string.Format("%{0}%", imSearch.Content));
                if (imSearch.StartDate.HasValue && imSearch.StartDate.Value != default(DateTime))
                    whereSql += " and DIMR.\"CreatedTime\">={0}".Params(imSearch.StartDate.Value);
                if (imSearch.EndDate.HasValue && imSearch.EndDate.Value != default(DateTime))
                    whereSql += " and DIMR.\"CreatedTime\"<={0}".Params(imSearch.EndDate.Value);
                if (!string.IsNullOrWhiteSpace(imSearch.Account))
                    whereSql += " and DIMR.\"LocalNumber\" = {0}".Params(imSearch.Account);
                if(!string.IsNullOrWhiteSpace(imSearch.TheOtherAccount))
                    whereSql += " and DIMR.\"RemoteNumber\" = {0}".Params(imSearch.TheOtherAccount);
                if (!string.IsNullOrWhiteSpace(imSearch.FileName))
                    whereSql += " and DIMR.\"FilePath\" = {0}".Params(imSearch.FileName);
            }
            return whereSql;
        }
    }
}